SELECT CON.CON_ANNO Anno_id,
       CON.CON_ID Contratto_id ,
       att.sog_ragsoc Ditributore,
       to_char(con.VER_DTINI,'dd/mm/yyyy') Data_inizio,
       con.TCON_DES Tipologia_uso,
       ATT.ATT_IDEXT Pod, 
       CON.SOG_RAGSOC Cliente,
       sog.SOG_CODFIS ||sog.SOG_PARIVA Codice_fiscale,
       to_char(con.CON_DTINIGES,'dd/mm/yyyy') Inizio_Gestione, 
       ubi.DECODE_INDID  Indirizzo_fornitura,
       (select valcx.vcatt_val from v_valcxatt valcx where valcx.az_id = '01' and  valcx.att_id= ubi.ATT_ID and valcx.STA_IDSYS =con.STA_IDSYS and valcx.tatt_id=3 and valcx.catt_id =3 and valcx.VER_DTFINE = to_date('31129999','ddmmyyyy'))Potenza_impegnata,
       (select valcx.vcatt_val from v_valcxatt valcx where valcx.az_id = '01' and  valcx.att_id= ubi.ATT_ID and valcx.STA_IDSYS =con.STA_IDSYS and valcx.tatt_id=3 and valcx.catt_id =2 and valcx.VER_DTFINE = to_date('31129999','ddmmyyyy'))Potenza_disponibile,
       (select valcx.vcatt_val from v_valcxatt valcx where valcx.az_id = '01' and  valcx.att_id= ubi.ATT_ID and valcx.STA_IDSYS =con.STA_IDSYS and valcx.tatt_id=3 and valcx.catt_id =6 and valcx.VER_DTFINE = to_date('31129999','ddmmyyyy'))Fasi,
       (select valcx.vcatt_val from v_valcxatt valcx where valcx.az_id = '01' and  valcx.att_id= ubi.ATT_ID and valcx.STA_IDSYS =con.STA_IDSYS and valcx.tatt_id=3 and valcx.catt_id =7 and valcx.VER_DTFINE = to_date('31129999','ddmmyyyy'))Volt,
       (select valcx.vcatt_val from v_valcxatt valcx where valcx.az_id = '01' and  valcx.att_id= ubi.ATT_ID and valcx.STA_IDSYS =con.STA_IDSYS and valcx.tatt_id=3 and valcx.catt_id =4 and valcx.VER_DTFINE = to_date('31129999','ddmmyyyy'))Tensione ,
     
       (select max(gmis.SMIS_MAT)
          from v_gmissmis gmis,
               v_comgmis com,
               v_grumis gru,
               v_attgmis attmis
         where gmis.COMGM_ID = com.COMGM_ID
         and gmis.az_id = '01'
         and com.az_id = '01'
         and gru.az_id = '01'
         and attmis.az_id = '01'
           and com.GMIS_ID = gru.GMIS_ID
           and gru.GMIS_ID = attmis.GMIS_ID
           and attmis.STA_IDSYS =con.STA_IDSYS
           and gru.STA_IDSYS =con.STA_IDSYS
           and com.STA_IDSYS =con.STA_IDSYS
           and gmis.STA_IDSYS =con.STA_IDSYS
           and attmis.ATT_ID = att.ATT_ID) Matricola,   
      
       (select mis.MIS_VALINI 
          from v_misura mis,
               v_gmissmis gmis,
               v_comgmis com,
               v_grumis gru,
               v_attgmis attmis
         where mis.GMSM_NUM = gmis.GMSM_NUM
          and mis.az_id = '01'
          and gmis.az_id = '01'
         and com.az_id = '01'
         and gru.az_id = '01'
         and attmis.az_id = '01'
           and gmis.COMGM_ID = com.COMGM_ID
           and com.GMIS_ID = gru.GMIS_ID
           and gru.GMIS_ID = attmis.GMIS_ID
           and attmis.STA_IDSYS =con.STA_IDSYS
           and gru.STA_IDSYS =con.STA_IDSYS
           and com.STA_IDSYS =con.STA_IDSYS
           and gmis.STA_IDSYS =con.STA_IDSYS
           and attmis.ATT_ID = att.ATT_ID
           and sysdate between attmis.VER_DTINI and attmis.VER_DTINI
           and sysdate between gmis.VER_DTINI and gmis.VER_DTFINE
           and sysdate between gru.VER_DTINI and gru.VER_DTFINE
           and sysdate between com.VER_DTINI and com.VER_DTFINE 
           and mis.fuco_id = 'ATT'
           and mis.FACO_ID ='FU') Lettura_ini_ATT_FU,  

         (select mis.MIS_VALINI 
          from v_misura mis,
               v_gmissmis gmis,
               v_comgmis com,
               v_grumis gru,
               v_attgmis attmis
         where mis.GMSM_NUM = gmis.GMSM_NUM
          and mis.az_id = '01'
          and gmis.az_id = '01'
         and com.az_id = '01'
         and gru.az_id = '01'
         and attmis.az_id = '01'
           and gmis.COMGM_ID = com.COMGM_ID
           and com.GMIS_ID = gru.GMIS_ID
           and gru.GMIS_ID = attmis.GMIS_ID
           and attmis.STA_IDSYS =con.STA_IDSYS
           and gru.STA_IDSYS =con.STA_IDSYS
           and com.STA_IDSYS =con.STA_IDSYS
           and gmis.STA_IDSYS =con.STA_IDSYS
           and attmis.ATT_ID = att.ATT_ID
           and sysdate between attmis.VER_DTINI and attmis.VER_DTINI
           and sysdate between gmis.VER_DTINI and gmis.VER_DTFINE
           and sysdate between gru.VER_DTINI and gru.VER_DTFINE
           and sysdate between com.VER_DTINI and com.VER_DTFINE 
           and mis.fuco_id = 'ATT'
           and mis.FACO_ID ='F1') Lettura_ini_ATT_F1,  
         (select mis.MIS_VALINI 
          from v_misura mis,
               v_gmissmis gmis,
               v_comgmis com,
               v_grumis gru,
               v_attgmis attmis
         where mis.GMSM_NUM = gmis.GMSM_NUM
          and mis.az_id = '01'
          and gmis.az_id = '01'
         and com.az_id = '01'
         and gru.az_id = '01'
         and attmis.az_id = '01'
           and gmis.COMGM_ID = com.COMGM_ID
           and com.GMIS_ID = gru.GMIS_ID
           and gru.GMIS_ID = attmis.GMIS_ID
           and attmis.STA_IDSYS =con.STA_IDSYS
           and gru.STA_IDSYS =con.STA_IDSYS
           and com.STA_IDSYS =con.STA_IDSYS
           and gmis.STA_IDSYS =con.STA_IDSYS
           and attmis.ATT_ID = att.ATT_ID
           and sysdate between attmis.VER_DTINI and attmis.VER_DTINI
           and sysdate between gmis.VER_DTINI and gmis.VER_DTFINE
           and sysdate between gru.VER_DTINI and gru.VER_DTFINE
           and sysdate between com.VER_DTINI and com.VER_DTFINE 
           and mis.fuco_id = 'ATT'
           and mis.FACO_ID ='F2') Lettura_ini_ATT_F2,  
         (select mis.MIS_VALINI 
          from v_misura mis,
               v_gmissmis gmis,
               v_comgmis com,
               v_grumis gru,
               v_attgmis attmis
         where mis.GMSM_NUM = gmis.GMSM_NUM
          and mis.az_id = '01'
          and gmis.az_id = '01'
         and com.az_id = '01'
         and gru.az_id = '01'
         and attmis.az_id = '01'
           and gmis.COMGM_ID = com.COMGM_ID
           and com.GMIS_ID = gru.GMIS_ID
           and gru.GMIS_ID = attmis.GMIS_ID
           and attmis.STA_IDSYS =con.STA_IDSYS
           and gru.STA_IDSYS =con.STA_IDSYS
           and com.STA_IDSYS =con.STA_IDSYS
           and gmis.STA_IDSYS =con.STA_IDSYS
           and attmis.ATT_ID = att.ATT_ID
           and sysdate between attmis.VER_DTINI and attmis.VER_DTINI
           and sysdate between gmis.VER_DTINI and gmis.VER_DTFINE
           and sysdate between gru.VER_DTINI and gru.VER_DTFINE
           and sysdate between com.VER_DTINI and com.VER_DTFINE            
           and mis.fuco_id = 'ATT'
           and mis.FACO_ID ='F3') Lettura_ini_ATT_F3,  
           (select mis.MIS_VALINI 
          from v_misura mis,
               v_gmissmis gmis,
               v_comgmis com,
               v_grumis gru,
               v_attgmis attmis
         where mis.GMSM_NUM = gmis.GMSM_NUM
          and mis.az_id = '01'
          and gmis.az_id = '01'
         and com.az_id = '01'
         and gru.az_id = '01'
         and attmis.az_id = '01'
           and gmis.COMGM_ID = com.COMGM_ID
           and com.GMIS_ID = gru.GMIS_ID
           and gru.GMIS_ID = attmis.GMIS_ID
           and attmis.STA_IDSYS =con.STA_IDSYS
           and gru.STA_IDSYS =con.STA_IDSYS
           and com.STA_IDSYS =con.STA_IDSYS
           and gmis.STA_IDSYS =con.STA_IDSYS
           and attmis.ATT_ID = att.ATT_ID
           and sysdate between attmis.VER_DTINI and attmis.VER_DTINI
           and sysdate between gmis.VER_DTINI and gmis.VER_DTFINE
           and sysdate between gru.VER_DTINI and gru.VER_DTFINE
           and sysdate between com.VER_DTINI and com.VER_DTFINE            
           and mis.fuco_id = 'REA'
           and mis.FACO_ID ='FU') Lettura_ini_REA_FU,  
         (select mis.MIS_VALINI 
          from v_misura mis,
               v_gmissmis gmis,
               v_comgmis com,
               v_grumis gru,
               v_attgmis attmis
         where mis.GMSM_NUM = gmis.GMSM_NUM
          and mis.az_id = '01'
          and gmis.az_id = '01'
         and com.az_id = '01'
         and gru.az_id = '01'
         and attmis.az_id = '01'
           and gmis.COMGM_ID = com.COMGM_ID
           and com.GMIS_ID = gru.GMIS_ID
           and gru.GMIS_ID = attmis.GMIS_ID
           and attmis.STA_IDSYS =con.STA_IDSYS
           and gru.STA_IDSYS =con.STA_IDSYS
           and com.STA_IDSYS =con.STA_IDSYS
           and sysdate between attmis.VER_DTINI and attmis.VER_DTINI
           and sysdate between gmis.VER_DTINI and gmis.VER_DTFINE
           and sysdate between gru.VER_DTINI and gru.VER_DTFINE
           and sysdate between com.VER_DTINI and com.VER_DTFINE            
           and gmis.STA_IDSYS =con.STA_IDSYS
           and attmis.ATT_ID = att.ATT_ID
           and mis.fuco_id = 'REA'
           and mis.FACO_ID ='F1') Lettura_ini_REA_F1,  
         (select mis.MIS_VALINI 
          from v_misura mis,
               v_gmissmis gmis,
               v_comgmis com,
               v_grumis gru,
               v_attgmis attmis
         where mis.GMSM_NUM = gmis.GMSM_NUM
           and mis.az_id = '01'
          and gmis.az_id = '01'
         and com.az_id = '01'
         and gru.az_id = '01'
         and attmis.az_id = '01'
         
           and gmis.COMGM_ID = com.COMGM_ID
           and com.GMIS_ID = gru.GMIS_ID
           and gru.GMIS_ID = attmis.GMIS_ID
           and attmis.STA_IDSYS =con.STA_IDSYS
           and gru.STA_IDSYS =con.STA_IDSYS
           and sysdate between attmis.VER_DTINI and attmis.VER_DTINI
           and sysdate between gmis.VER_DTINI and gmis.VER_DTFINE
           and sysdate between gru.VER_DTINI and gru.VER_DTFINE
           and sysdate between com.VER_DTINI and com.VER_DTFINE            
           and com.STA_IDSYS =con.STA_IDSYS
           and gmis.STA_IDSYS =con.STA_IDSYS
           and attmis.ATT_ID = att.ATT_ID
           and mis.fuco_id = 'REA'
           and mis.FACO_ID ='F2') Lettura_ini_REA_F2,  
         (select mis.MIS_VALINI 
          from v_misura mis,
               v_gmissmis gmis,
               v_comgmis com,
               v_grumis gru,
               v_attgmis attmis
         where mis.GMSM_NUM = gmis.GMSM_NUM
          and mis.az_id = '01'
          and gmis.az_id = '01'
         and com.az_id = '01'
         and gru.az_id = '01'
         and attmis.az_id = '01'
           and gmis.COMGM_ID = com.COMGM_ID
           and com.GMIS_ID = gru.GMIS_ID
           and gru.GMIS_ID = attmis.GMIS_ID
           and attmis.STA_IDSYS =con.STA_IDSYS
           and gru.STA_IDSYS =con.STA_IDSYS
           and com.STA_IDSYS =con.STA_IDSYS
           and gmis.STA_IDSYS =con.STA_IDSYS
           and attmis.ATT_ID = att.ATT_ID
           and sysdate between attmis.VER_DTINI and attmis.VER_DTINI
           and sysdate between gmis.VER_DTINI and gmis.VER_DTFINE
           and sysdate between gru.VER_DTINI and gru.VER_DTFINE
           and sysdate between com.VER_DTINI and com.VER_DTFINE            
           and mis.fuco_id = 'REA'
           and mis.FACO_ID ='F3') Lettura_ini_REA_F3, 
         
       (select max(consogg.SOG_RAGSOC) from v_consog consogg  
       where con.CON_ANNO =consogg.CON_ANNO  and con.CON_ID = consogg.CON_ID  and consogg.az_id = con.az_id
       and consogg.STA_IDSYS = con.STA_IDSYS  and consogg.rSOG_ID = 9) Agente,
     
       con.TATTI_DES Tipologia_attivazione,
       CON.STA_IDSYS Stato_contratto,
       val.VCCUF_VAL Data_presunta_attivazione
FROM V_CONTRATTO CON,
     V_CONUBIFRN UBI,
     V_ATTACCO ATT, 
     v_grumis gru , 
     v_grumis_sel grusel,
     v_valcxcuf val,
     soggetto sog
WHERE    UBI.CON_ANNO=CON.CON_ANNO
and con.az_id = '01'
and ubi.az_id = '01'
and att.az_id = '01'
and gru.az_id = '01'
and grusel.az_id = '01'
and val.az_id = '01'
and sog.az_id = '01'
and sog.az_id = con.az_id
     AND UBI.CON_ID=CON.CON_ID
     and ubi.CON_ANNO=con.CON_ANNO
     AND UBI.ATT_ID= ATT.ATT_ID                 
     and val.STA_IDSYS =con.STA_IDSYS
     and sog.SOG_ID = con.SOG_ID
     and con.SER_ID=3
     and ubi.SER_ID=con.ser_id
     and val.CUF_ID = ubi.CUF_ID
     and val.CCON_ID = 22
     and gru.gmis_id=grusel.GMIS_ID
     and con.STA_IDSYS  in (1,2)
     AND gru.ATT_ID= ATT.ATT_ID
     and grusel.STA_IDEXT != 5
     and sysdate between gru.VER_DTINI and gru.VER_DTFINE
order by con.VER_DTINI 
